from pyecharts.charts import Line, Page
import pyecharts.options as opts
from pyecharts.globals import ThemeType
from sqlalchemy import create_engine
import pandas as pd
import sys
import numpy as np

sys.path.append("..//database")
from SqlWizard import *
from CsvWizard import *
from CmnFuncs import *


def run_crude():
    '''
    本模块包含品种：原油、燃料油、沥青
    设计图表：
    1. 原油期货主连收盘价表；
    2. 沥青现货基差表；
    3. 沥青主力次主力月差表，次主力与三号月差表
    4. 燃料油主力次主力月差表， 燃料油次主力与三号合约月差表
    5. 沥青与原油主力比价表
    6。 燃油与沥青主力比价表 次主力比价表， 三号比价表
    '''

    a_list = date_checker_1()
    c_list = date_checker_3()

    sc_main = read_sql_fut('fut_scl')
    wti = read_csv_ivstng("C://Users//Daniel//Documents//ProjectStarGaze//dataprocessor//ivstngdata//WTI原油期货历史数据.csv")
    brt = read_csv_ivstng("C://Users//Daniel//Documents//ProjectStarGaze//dataprocessor//ivstngdata//伦敦布伦特原油期货历史数据.csv")
    fu_spdrdx = read_sql_spdrdx('spdrdex_fu')
    fu_index = read_sql_fut_index('fut_index_fu')
    fu_ry = read_sql_ry('roll_yield_fu')
    fu_1 = read_sql_fut('fut_fu{}'.format(a_list[0]))
    fu_2 = read_sql_fut('fut_fu{}'.format(a_list[1]))
    fu_3 = read_sql_fut('fut_fu{}'.format(a_list[2]))
    bu_spot = read_sql_spot('spot_bu')
    bu_index = read_sql_fut_index('fut_index_bu')
    bu_ry = read_sql_ry('roll_yield_bu')
    bu_spdrdx = read_sql_spdrdx('spdrdex_bu')
    bu_1 = read_sql_fut('fut_bu{}'.format(c_list[0]))
    bu_2 = read_sql_fut('fut_bu{}'.format(c_list[1]))
    bu_3 = read_sql_fut('fut_bu{}'.format(c_list[2]))
    pg_spot = read_sql_spot('spot_pg')
    pg_index = read_sql_fut_index('fut_index_pg')
    pg_ry = read_sql_ry('roll_yield_pg')
    pg_spdrdx = read_sql_spdrdx('spdrdex_pg')

    # 原油
    engine = create_engine("mysql+pymysql://root:dannysql@localhost:3306/tradedata?charset=utf8mb4")
    eia_df = pd.read_sql_table('funda_us_eia_crude', con=engine, index_col='index')
    dlist = eia_df.index.to_list()
    eiacrude = eia_df['eia_crude_rate'].to_list()
    crude_np = np.array(eiacrude).astype(float)
    ave = np.nanmean(crude_np)
    upper = ave + np.nanstd(crude_np)
    lower = ave - np.nanstd(crude_np)
    eialine = (
        Line()
        .add_xaxis(dlist)
        .add_yaxis('EIA-Crude', eiacrude,
                   markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(name='均线', y=ave),
                                                         opts.MarkLineItem(name='上线', y=upper),
                                                         opts.MarkLineItem(name='下线', y=lower), ])
                   )
        .set_global_opts(title_opts=opts.TitleOpts(title="EIA原油库存报告"),
                         yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                         datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )

    header_sc = draw_separator('原油')
    # 1. sc, wti, brent三原油期货主连收盘价表， 双y轴
    ser_sc = sc_main['close']
    ser_brt = brt['收盘']
    ser_wti = wti['收盘']
    datelist = (ser_sc + ser_brt + ser_wti).index.to_list()
    price_sc, price_brt, price_wti = [], [], []

    def try_get(seri, d):
        try:
            val = seri[d]
        except:
            val = None
        else:
            pass
        return val

    for date in datelist:
        price_sc.append(try_get(ser_sc, date))
        price_brt.append(try_get(ser_brt, date))
        price_wti.append(try_get(ser_wti, date))

    crude_lines = (
        Line(init_opts=opts.InitOpts(theme=ThemeType.INFOGRAPHIC))
        .add_xaxis(datelist)
        .add_yaxis('上海原油SC', price_sc, is_connect_nones=True)
        .extend_axis(yaxis=opts.AxisOpts(is_scale=True))
        .add_yaxis('BRENT原油', price_brt, yaxis_index=1, is_connect_nones=True)
        .add_yaxis('WTI原油', price_wti, yaxis_index=1, is_connect_nones=True)
        .set_global_opts(yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                         datazoom_opts=opts.DataZoomOpts(range_start=0, range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )

    # 沥青
    header_bu = draw_separator('沥青')
    # 沥青远期曲线
    future_curve_bu = draw_future_curve(bu_spot, bu_1, bu_2, bu_3)
    # 沥青一致性预期指标
    idctr_bu = draw_spec_idctr('沥青', bu_spdrdx, bu_index, bu_ry)
    # 2. 沥青现货基差图
    bu_spot_chart = draw_spot_spd('沥青', bu_spot)
    # 沥青合约价格、成交量、持仓量、持仓量变化图
    bu_contract_1 = draw_contract('bu{}'.format(c_list[0]), bu_1)
    bu_contract_2 = draw_contract('bu{}'.format(c_list[1]), bu_2)
    bu_contract_3 = draw_contract('bu{}'.format(c_list[2]), bu_3)
    # 3. 沥青合约间月差图
    bu_12_chart = draw_spd('bu{}'.format(c_list[0]), bu_1['close'], 'bu{}'.format(c_list[1]), bu_2['close'])
    bu_23_chart = draw_spd('bu{}'.format(c_list[1]), bu_2['close'], 'bu{}'.format(c_list[2]), bu_3['close'])

    # 燃料油
    header_fu = draw_separator('燃料油')
    # 燃料油一致性预期指标
    idctr_fu = draw_spec_idctr('燃料油', fu_spdrdx, fu_index, fu_ry)
    # 燃料油合约价格、成交量、持仓量、持仓量变化图
    fu_contract_1 = draw_contract('fu{}'.format(a_list[0]), fu_1)
    fu_contract_2 = draw_contract('fu{}'.format(a_list[1]), fu_2)
    fu_contract_3 = draw_contract('fu{}'.format(a_list[2]), fu_3)
    # 4. 燃料油合约间月差图
    fu_12_chart = draw_spd('fu{}'.format(a_list[0]), fu_1['close'], 'fu{}'.format(a_list[1]), fu_2['close'])
    fu_23_chart = draw_spd('fu{}'.format(a_list[1]), fu_2['close'], 'fu{}'.format(a_list[2]), fu_3['close'])

    header_pg = draw_separator('LPG')
    # pg现货基差图
    pg_spot_chart = draw_spot_spd('LPG', pg_spot, 1)
    # pg一致性预期指标
    idctr_pg = draw_spec_idctr('LPG', pg_spdrdx, pg_index, pg_ry)

    # 比价区
    header_cross = draw_separator('品种间')
    # wti与brent收盘价曲线以及brt对wti价差
    dif_brt_wti = draw_price_dif('Brent', brt['收盘'], 'WTI', wti['收盘'])

    # 5. 沥青与原油主连价比
    ratio_bu_sc = draw_price_ratio('沥青{}'.format(c_list[0]), bu_1['close'], '原油主连', sc_main['close'], 1)

    # 燃料油与原油主连比价
    ratio_fu_sc = draw_price_ratio('燃料油{}'.format(a_list[0]), fu_1['close'], '原油主连', sc_main['close'], 1)

    # 6. 燃油与沥青主力比价表
    arbi_record('fu', fu_index['close'], 'bu', bu_index['close'], x=1, y=1, opt=1, cat=1)
    ratio_fu_bu_1 = draw_price_ratio('fu{}'.format(a_list[0]), fu_1['close'],
                                     'bu{}'.format(c_list[0]), bu_1['close'], 3)
    ratio_fu_bu_2 = draw_price_ratio('fu{}'.format(a_list[1]), fu_2['close'],
                                     'bu{}'.format(c_list[1]), bu_2['close'], 3)
    ratio_fu_bu_3 = draw_price_ratio('fu{}'.format(a_list[2]), fu_3['close'],
                                     'bu{}'.format(c_list[2]), bu_3['close'], 3)

    page = Page(page_title='原油链', layout=Page.SimplePageLayout)
    page.add(
        header_sc,
        crude_lines,
        eialine,
        header_bu,
        future_curve_bu,
        idctr_bu,
        bu_spot_chart,
        bu_contract_1,
        bu_contract_2,
        bu_contract_3,
        bu_12_chart,
        bu_23_chart,
        header_fu,
        idctr_fu,
        fu_contract_1,
        fu_contract_2,
        fu_contract_3,
        fu_12_chart,
        fu_23_chart,
        header_pg,
        pg_spot_chart,
        idctr_pg,
        header_cross,
        dif_brt_wti,
        ratio_fu_sc,
        ratio_bu_sc,
        ratio_fu_bu_1,
        ratio_fu_bu_2,
        ratio_fu_bu_3
    )
    page.render('原油链.html')

